Character data¶

Character data (strings) may be stored with VARCHAR(STRING), CHAR, ENUM, and they support charset and collation.

Charset(character set, codeset) controls how characters are stored (on any type of storage) as bytes, or how a series of bytes forms a character. CUBRID supports ISO-88591-1, UTF-8 and EUC-KR charsets. For UTF-8, we support only the Unicode characters up to codepoint 10FFFF (encoded on up to four bytes). For instance, the character "Ç" is encoded in codeset ISO-8859-1 using a single byte (C7), in UTF-8 is encoded with 2 bytes (C3 88), while in EUC-KR this character is not available.

Collation decides how strings compare. Most often, users require case insensitive and case sensitive comparisons. For instance, the strings "ABC" and "abc" are equal in a case insensitive collation, while in a case sensitive collation, they are not, and depending on other collation settings, the relationship can be "ABC" < "abc" , or "ABC" > "abc".

Collation means more than comparing character casing. Collation decides the relationship between two strings (greater, lower, equal), is used in string matching (LIKE), or computing boundaries in index scan.

In CUBRID, a collation implies a charset. For instance, collations "utf8_en_ci" and "iso88591_en_ci" perform case insensitive compare, but operate on different charsets. Although for ASCII range, in these particular cases the results are similar, the collation with "utf8_en_ci" is slower, since it needs to work on variable number of bytes (UTF-8 encoding).

• "'a' COLLATE iso88591_en_ci" indicates "_iso88591'a' COLLATE iso88591_en_ci".
• "'a' COLLATE utf8_en_ci" indicates "_utf8'a' COLLATE utf8_en_ci".

All string data types support precision. Special care is required with fixed characters(CHAR). The values of this types are padded to fill up the precision. For instance, inserting "abc" into a CHAR(5) column, will result in storing "abc " (2 padding spaces are added). Space (ASCII 32 decimal, Unicode 0020) is the padding character for most charsets. But, for EUC-KR charset, the padding consists of one character which is stored with two bytes (A1 A1).

Related Terms¶

• Character set: A group of encoded symbols (giving a specific number to a certain symbol)

• Collation: A set of rules for comparison of characters in the character set and for sorting data

• Locale: A set of parameters that defines any special variant preferences such as number format, calendar format (month and day in characters), date/time format, and collation depending on the operator's language and country. Locale defines the linguistic localization. Character set of locale defines how the month in characters and other data are encoded. A locale identifier consists of at least a language identifier and a region identifier, and it is expressed as language[_territory][.codeset] (For example, Australian English using UTF-8 encoding is written as en_AU.UTF-8).

• Unicode normalization: The specification by the Unicode character encoding standard where some sequences of code points represent essentially the same character. CUBRID uses Normalization Form C (NFC: codepoint is decomposed and then composed) for input and Normalization Form D (NFD: codepoint is composed and then decomposed) for output. However, CUBRID does not apply the canonical equivalence rule as an exception.

  For example, canonical equivalence is applied in general NFC rule so codepoint 212A (Kelvin K) is converted to codepoint 4B (ASCII code uppercase K). Since CUBRID does not perform the conversion by using the canonical equivalence rule to make normalization algorithm quicker and easier, it does not perform reverse-conversion, too.

• Canonical equivalence: A basic equivalence between characters or sequences of characters, which cannot be visually distinguished when they are correctly rendered. For example, let's see 'Å' ('A' with an angstrom). 'Å' (Unicode U + 212B) and Latin 'A' (Unicode U + 00C5) have same A and different codepoints, however, the decomposed result is 'A' and U+030A, so it is canonical equivalence.

• Compatibility equivalence: A weaker equivalence between characters or sequences of characters that represent the same abstract character. For example, let's see number '2' (Unicode U + 0032) and superscript '²'(Unicode U + 00B2). '²' is a different format of number '2', however, it is visually distinguished and has a different meaning, so it is not canonical equivalence. When normalizing '2²' with NFC, '2²' is maintained since it uses canonical equivalence. However, with NFKC, '²' is decomposed to '2' which is compatibility equivalence and then it can be recomposed to '22'. Unicode normalization of CUBRID does not apply the compatibility equivalence rule.

For explanation on Unicode normalization, see Unicode Normalization. For more details, see http://unicode.org/reports/tr15/.

The default value of the system parameter related to Unicode normalization is unicode_input_normalization=no and unicode_output_normalization=no. For a more detailed description on parameters, see Statement/Type-Related Parameters.

Locale Attributes¶

Locale is defined by following attributes.

• Charset (codeset): How bytes are interpreted into single characters (Unicode codepoints)
• Collations: Among all collations defined in locale of LDML(UNICODE Locale Data Markup Language) file, the last one is the default collation. Locale data may contain several collations.
• Alphabet (casing rules): One locale data may have up 2 alphabets, one for identifier and one for user data. One locale data can have two types of alphabets.
• Calendar: Names of weekdays, months, day periods (AM/PM)
• Numbering settings: Symbols for digit grouping
• Text conversion data: For CSQL conversion. Option.
• Unicode normalization data: Data converted by normalizing several characters with the same shape into one based on a specified rule. After normalization, characters with the same shape will have the same code value even though the locale is different. Each locale can activate/deactivate the normalization functionality.

Collation Properties¶

A collation is an assembly of information which defines an order for characters and strings. In CUBRID, collation has the following properties.

• Strength: This is a measure of how "different" basic comparable items (characters) are. This affects selectivity. In LDML files, collation strength is configurable and has four levels. For example a Case insensitive collation should be set with level = "secondary" (2) or "primary" (1).
• Whether it supports or not expansions and contractions

Each column has a collation, so when applying LOWER(), UPPER() functions the casing rules of locale which defines the collation's default language is used.

Depending on collation properties some CUBRID optimizations may be disabled for some collations:

• LIKE rewrite: is disabled for collations which maps several different character to the same weight (case insensitive collations for example) and for collations with expansions.
• Covering index scan: disabled for collations which maps several different character to the same weight (see Covering Index).

For more information, see Collation settings impacting CUBRID features .

Collation Naming Rules¶

The collation name in CUBRID follows the conversion:

<charset>_<lang specific>_<desc1>_<desc2>_...

• <charset>: The full charset name as used by CUBRID. iso88591, utf8, euckr.
• <lang specific>: a region/language specific. The language code is expected as two characters; en, de, es, fr, it, ja, km, ko, tr, vi, zh, ro. "gen" if it does not address a specific language, but a more general sorting rule.
• <desc1>_<desc2>_...: They have the following meaning. Most of them apply only to LDML collations.
  • ci: case insensitive In LDML, can be obtained using the settings: strength="secondary" caseLevel="off" caseFirst="off".
  • cs: case sensitive; By default all collations are case sensitive. In LDML, can be obtained using at least: strength="tertiary".
  • bin: it means that the sorting order under such collation is almost the same with the order of codepoints; If memory (byte) comparison is used, then almost the same result is obtained. Space character and EUC double-byte padding character are always sorted as zero in "bin" collation. No collations with such setting are currently configured in LDML (they are already available as built-in), but a similar one can be obtained using the maximum setting strength="quaternary" or strength="identical".
  • ai: accent insensitive; this means that 'Á' is sorted the same as 'A'. Due to particularities of the UCA based algorithms, an accent insensitive collation is also a case insensitive collation. In LDML, can be obtained using: strength="primary".
  • uca: this signals a UCA based collation; this is used only to differentiate such collations from similar built-in variants. All LDML collations are based on UCA, but in order to keep shorter names only two collations ( 'utf8_ko_cs_uca' , 'utf8_tr_cs_uca' ) have this description in their names, in order to differentiate them from 'utf8_ko_cs' and 'utf8_tr_cs' collations.
  • exp: this collations use a full-word matching/compare algorithm, contrary to the rest of collations which use character-by-character compare. This collation uses a more complex algorithm, with multiple passes which is much slower, but may prove useful for alphabetical sorts. In LDML, the Expansion needs to be explicit by adding CUBRIDExpansions="use".
  • ab: accent backwards; it is particularity of French-Canadian sorting, where level 2 of UCA (used to store accents weights) is compared from end of string towards the beginning. This collation setting can be used only when :ref`expansion` setting is also activated. The "backwards" setting allows for the following sorting:
    • Normal Accent Ordering: cote < coté < côte < côté
    • Backward Accent Ordering: cote < côte < coté < côté
  • cbm: contraction boundary match; it is a particularity of collations with Expansion and Contraction and refers to how it behaves at string matching when a Contraction is found. Suppose the collation has defined the Contraction "ch"; then normally, the pattern "bac" will not match the string"bachxxx" But when the collation is configured to allow "matching the characters starting a contraction", the above matching will return a positive. Only one collation is configured in this manner - 'utf8_ja_exp_cbm' - Japanese sorting requires a lot of contractions.

The collation names are not dynamically generated. They are user defined (configured in LDML), and should reflect the settings of the collation.

The name of collation influences the internal numeric id of the collation. For instance, in CUBRID only 256 collations are allowed, and the numeric IDs are assigned as:

• 0 -31: built-in collations (for these collations the name and id are hard-coded)
• 32 - 46: LDML collations having "gen" as "language" part
• 47 - 255: the rest of LDML collations

If you want to include all locales into the database which CUBRID provide, first, copy cubrid_locales.all.txt of $CUBRID/conf directory into cubrid_locales.txt and next, run make_locale script(in extension, Linux is .sh, Windows is .bat). For more details on make_locale script, see Step 2: Compiling Locale.

If you want to include the newly added locale information into the existing database, run "cubrid synccolldb <dbname>". For more information, see Synchronization of Database Collations with System Collations.

If you include all locales defined in LDML files, CUBRID has the following collations.

Files For Locale Setting¶

CUBRID uses following directories and files to set the locales.

• $CUBRID/conf/cubrid_locales.txt file: A configuration file containing the list of locales to be supported
• $CUBRID/conf/cubrid_locales.all.txt file: A configuration file template with the same structure as cubrid_locales.txt. Contains the entire list of all the locales that the current version of CUBRID is capable of supporting without any efforts from the end user's side.
• $CUBRID/locales/data directory: This contains files required to generate locale data.
• $CUBRID/locales/loclib directory: contains a C header file, locale_lib_common.h and OS dependent makefile which are used in the process of creating / generating locales shared libraries.
• $CUBRID/locales/data/ducet.txt file: Text file containing default universal collation information (codepoints, contractions and expansions, to be more specific) and their weights, as standardized by The Unicode Consortium, which is the starting point for the creation of collations. For more information, see http://unicode.org/reports/tr10/#Default_Unicode_Collation_Element_Table .
• $CUBRID/locales/data/unicodedata.txt file: Text file containing information about each Unicode codepoint regarding casing, decomposition, normalization etc. CUBRID uses this to determine casing. For more information, see http://www.ksu.ru/eng/departments/ktk/test/perl/lib/unicode/UCDFF301.html .
• $CUBRID/locales/data/ldml directory: common_collations.xml and XML files, name with the convention cubrid_<locale_name>.xml. common_collations.xml file contains shared collation information in all locale files, and each cubrid_<locale_name>.xml file contains a locale information for the supported language.
• $CUBRID/locales/data/codepages directory: contains codepage console conversion for single byte codepages(8859-1.txt , 8859-15.txt, 8859-9.txt) and codepage console conversion for double byte codepages(CP1258.txt , CP923.txt, CP936.txt , CP949.txt).
• $CUBRID/bin/make_locale.sh file or %CUBRID%\bin\make_locale.bat file: A script file used to generate shared libraries for locale data
• $CUBRID/lib directory: Shared libraries for generated locales will be stored here.

Step 1: Selecting a Locale¶

Configure locales to use on $CUBRID/conf/cubrid_locales.txt. You can select all or some of locales which are supported.

CUBRID supports locales as follows: en_US, de_DE, es_ES, fr_FR, it_IT, ja_JP, km_KH, ko_KR, tr_TR, vi_VN, zh_CN, ro_RO.

The language and country for each locale are shown in the following table.

<lang_name>  <LDML file>                                        <lib file>
ko_KR        /home/CUBRID/locales/data/ldml/cubrid_ko_KR.xml    /home/CUBRID/lib/libcubrid_ko_KR.so

Step 2: Compiling Locale¶

Once the requirements described above are met, the locales can be compiled.

Regarding the embedded locales in CUBRID, they can be used without compiling user locale library, so they can be used by skipping the step 2. But there are differences between the embedded locale and the library locale. Regarding this, see Built-in Locale and Library Locale.

To compile the locale libraries, one must use the make_locale (.bat for Windows and .sh for Linux) utility script from command console. The file is delivered in $CUBRID/bin folder so it should be resolved by $PATH environment variable. Here $CUBRID, $PATH are the environment variables of Linux, %CUBRID%, %PATH% are the environment variables of Windows.

Usage can be displayed by running make_locale.sh -h. (make_locale /h in Windows.)

make_locale.sh [options] [locale]

options ::= [-t 32|64 ] [-m debug|release]
locale ::= [de_DE|es_ES|fr_FR|it_IT|ja_JP|km_KH|ko_KR|tr_TR|vi_VN|zh_CN|ro_RO]

• options
  • -t: Selects 32bit or 64bit (default value: 64).
  • -m: Selects release or debug. In general, release is selected (default value: release). The debug mode is provided for developers who would like to write the locale library themselves. Selects release or debug. In general, release is selected (default value: release). The debug mode is provided for developers who would like to write the locale library themselves.
• locale: The locale name of the library to build. If locale is not specified, the build includes data from all configured locales. In this case, library file is stored in $CUBRID/lib directory with the name of libcubrid_all_locales.so (.dll for Windows).

To create user defined locale shared libraries, two choices are available:

• Creating a single lib with all locales to be supported.

  make_locale.sh -t64                        # Build and pack all locales (64/release)
  

• Creating one lib for each locale to be supported.

  make_locale.sh -t 64 -m release ko_KR
  

The first choice is recommended. In this scenario, some data may be shared among locales. If you choose the first one, a lib supporting all locales has less than 15 MB; in the second one, consider for each locale library from 1 MB to more than 5 MB. Also the first one is recommended because it has no runtime overhead during restarting the servers when you choose the second one.

Step 3: Setting CUBRID to Use a Specific Locale¶

Only one locale can be selected as the default locale when you create DB.

In addition to the possibility of specifying a default locale, one can override the default calendar settings with the calendar settings from another locale, using the intl_date_lang system parameter.

• The locale will be in the format: <locale_name>.[utf8 | iso] (e.g. tr_TR.utf8, en_EN.ISO, ko_KR.utf8)
• intl_date_lang: <locale_name>. The possible values for <locale_name> are listed on Step 1: Selecting a Locale.

Step 4: Creating a Database with the Selected Locale Setting¶

When issuing the command "cubrid createdb <db_name> <locale_name.charset>", a database will be created using the settings in the variables described above.

Once the database is created a locale setting which was given to the database cannot be changed. The charset and locale name are stored in "db_root" system catalog table.

Step 5 (optional): Manually Verifying the Locale File¶

The contents of locales libraries may be displayed in human readable form using the dumplocale CUBRID utility. Execute cubrid dumplocale -h to output the usage. The used syntax is as follows.

cubrid dumplocale [options] [language-string]

options ::= -i|--input-file <shared_lib>
            -d|--calendar
            -n|--numeric
            {-a |--alphabet=}{l|lower|u|upper|both}
            -c|--codepoint-order
            -w|--weight-order
            {-s|--start-value} <starting_codepoint>
            {-e|--end-value} <ending_codepoint>
            -k
            -z

language-string ::= de_DE|es_ES|fr_FR|it_IT|ja_JP|km_KH|ko_KR|tr_TR|vi_VN|zh_CN|ro_RO

• dumplocale: A command which dumps the contents of locale shared library previously generated using LDML input file.
• language-string: One of de_DE, es_ES, fr_FR, it_IT, ja_JP, km_KH, ko_KR, tr_TR, vi_VN, zh_CN and ro_RO. Configures the locale language to dump the locale shared library. If it's not set, all languages which are configured on cubrid_locales.txt are given.

The following are [options] for cubrid dumplocale.

-i, --input-file=FILE¶

The name of the locale shared library file (< shared_lib>) created previously. It includes the directory path.

-d, --calendar¶

Dumps the calendar and date/time data. Default value: No

-n, --numeric¶

Dumps the number data. Default value: No

-a, --alphabet=l|lower|u|upper|both¶

Dumps the alphabet and case data. Default value: No

--identifier-alphabet=l|lower|u|upper¶

Dumps the alphabet and case data for the identifier. Default value: No

-c, --codepoint-order¶

Dumps the collation data sorted by the codepoint value. Default value: No (displayed data: cp, char, weight, next-cp, char and weight)

-w, --weight-order¶

Dumps the collation data sorted by the weight value. Default value: No (displayed data: weight, cp, char)

-s, --start-value=CODEPOINT¶

Specifies the dump scope. Starting codepoint for -a, --identifier-alphabet, -c, -w options. Default value: 0

-e, --end-value=CODEPOINT¶

Specifies the dump scope. Ending codepoint for -a, --identifier-alphabet, -c, -w options. Default value: Max value read from the locale shared library.

-k, --console-conversion¶

Dumps the data of console conversion. Default value: No

-z, --normalization¶

Dumps the normalization data. Default value: No

The following example shows how to dump the calendar, number formatting, alphabet and case data, alphabet and case data for the identifier, collation sorting based on the codepoint order, collation sorting based on the weight, and the data in ko_KR locale into ko_KR_dump.txt by normalizing:

% cubrid dumplocale -d -n -a both -c -w -z ko_KR > ko_KR_dump.txt

It is highly recommended to redirect the console output to a file, as it can be very big data, and seeking information could prove to be difficult.

Step 6: Starting CUBRID-Related Processes¶

All CUBRID-related processes should be started in an identical environmental setting. The CUBRID server, the broker, CAS, and CSQL should use the locale binary file of an identical version. Also CUBRID HA should use the same setting. For example, in the CUBRID HA, master server, slave server and replica server should use the same environmental variable setting.

There is no check on the compatibility of the locale used by server and CAS (client) process, so the user should make sure the LDML files used are the same.

Locale library loading is one of the first steps in CUBRID start-up. Locale (collation) information is required for initializing databases structures (indexes depends on collation). This process is performed by each CUBRID process which requires locale information: server, CAS, CSQL, createdb, copydb, unload, load DB.

The process of loading a locale library is as follows.

• If no lib path is provided, CUBRID will try to load $CUBRID/lib/libcubrid_<lang_name>.so file; if this file is not found, then CUBRID assumes all locales are found in a single library: $CUBRID/lib/libcubrid_all_locales.so.
• If suitable locale library cannot be found or any other error occurs during loading, the CUBRID process stops.
• If collations between the database and the locale library are different, the CUBRID process cannot start. To include the newly changed collations of the locale library, firstly synchronize the database collation with the system collation by running cubrid synccolldb command. Next, update from the existing database to the wanted collations of schemas and data. For more details, see Synchronization of Database Collations with System Collations.

Synchronization of Database Collations with System Collations¶

CUBRID's normal operation requires that the system collation and the database collation must be the same. The system locale means that the locale which include built-in locales and library locales created through cubrid_locales.txt (see Locale Setting), and it includes the system collation information. The database collation information is stored on the _db_collation system catalog table.

cubrid synccolldb utility checks if the database collation is the same with the system collation, and synchronize into the system collation if they are different. However, note that this utility doesn't transform the data itself stored on the database.

This utility can be used when the existing database collation should be changed after the system locale is changed. However, there are operations which the user have to do manually.

The user should do this operations before the synchronization. These operations can be done by running CSQL with cubrid_synccolldb_<database_name>.sql file, which is created by cubrid synccolldb -c.

• Change collation using ALTER TABLE .. MODIFY statement.
• Remove any views, indexes, triggers or partitions containing the collation.

Run synchrization with cubrid synccolldb. After then, do the following operations.

• Recreate views, indexes, triggers, or partitions
• Update application statements to use new collations

This utility should work only in offline mode.

synccolldb syntax is as follows.

cubrid synccolldb [options] database_name

• cubrid: An integrated utility for the CUBRID service and database management.
• synccolldb: A command to synchronize collations of a database with collations from the system(according to contents of locales libraries and $CUBRID/conf/cubrid_locales.txt).
• database_name: A database name to be synchronized with collations from the system.

If [options] is omitted, synccolldb checks the collation differences between the system and the database, synchronize the database collation with the system collation, and create the cubrid_synccolldb_<database_name>.sql file including the queries of objects to be dropped before the synchronization.

The following are [options] which are used on cubrid synccolldb.

-c, --check-only¶

This option prints out the collation information which is different between the database collation and the system collation.

-f, --force-only¶

This option doesn't ask when updating the database collation with the system collation.

The following shows that how it works when the system collation and the database collation are different.

Firstly, make locale library about ko_KR locale.

$ echo ko_KR > $CUBRID/conf/cubrid_locales.txt
$ make_locale.sh -t 64

Next, create the database.

$ cubrid createdb --db-volume-size=20M --log-volume-size=20M xdb en_US

Create a schema. At this time, specify the needed collation in each table.

$ csql -S -udba xdb -i in.sql

CREATE TABLE dept(depname STRING PRIMARY KEY) COLLATE utf8_ko_cs_uca;
CREATE TABLE emp(eid INT PRIMARY KEY, depname STRING,address STRING) COLLATE utf8_ko_cs_uca;
ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY (depname) REFERENCES dept(depname);

Change the locale setting of the system. If you do not any values on cubrid_locales.txt, the database consider that only built-in locales exist

$ echo "" > $CUBRID/conf/cubrid_locales.txt

Check the difference between system and database by running cubrid synccolldb -c command.

$ cubrid synccolldb -c xdb

----------------------------------------
----------------------------------------
Collation 'utf8_ko_cs_uca' (Id: 133) not found in database or changed in new system configuration.
----------------------------------------
----------------------------------------
Collation 'utf8_gen_ci' (Id: 44) not found in database or changed in new system configuration.
----------------------------------------
----------------------------------------
Collation 'utf8_gen_ai_ci' (Id: 37) not found in database or changed in new system configuration.
----------------------------------------
----------------------------------------
Collation 'utf8_gen' (Id: 32) not found in database or changed in new system configuration.
----------------------------------------
----------------------------------------
There are 4 collations in database which are not configured or are changed compared to system collations.
Synchronization of system collation into database is required.
Run 'cubrid synccolldb -f xdb'

If the indexes exist, firstly you should remove the indexes, and change the collation of each table, then recreate the indexes directly. The process to remove indexes and change the collation of tables can be executed by using cubrid_synccolldb_xdb.sql file which was created by synccolldb command. On the below example, a foreign key is the index which you should recreate.

$ cat cubrid_synccolldb_xdb.sql

ALTER TABLE [dept] COLLATE utf8_bin;
ALTER TABLE [emp] COLLATE utf8_bin;
ALTER TABLE [emp] DROP FOREIGN KEY [fk_emp_depname];
ALTER TABLE [dept] MODIFY [depname] VARCHAR(1073741823) COLLATE utf8_bin;
ALTER TABLE [emp] MODIFY [address] VARCHAR(1073741823) COLLATE utf8_bin;
ALTER TABLE [emp] MODIFY [depname] VARCHAR(1073741823) COLLATE utf8_bin;

$ csql -S -u dba -i cubrid_synccolldb_xdb.sql xdb

Removing the obsolete collations by executing the above cubrid_synccolldb_xdb.sql script file must be performed before forcing the synchronization of system collations into database.

Run cubrid synccolldb command. If the option is omitted, the message is shown to ask to run this command or not; if the -f option is given, the synchronization is run without checking message.

$ cubrid synccolldb xdb
Updating system collations may cause corruption of database. Continue (y/n) ?
Contents of '_db_collation' system table was updated with new system collations.

Recreate the dropped foreign key.

$ csql -S -u dba xdb

ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY fk_emp_depname(depname) REFERENCES dept(depname);

Charset and Collation of Column¶

Charset and Collation apply to string data types: VARCHAR (STRING), CHAR and ENUM. By default, all string data types inherit the default database collation and character set, but CUBRID supports two modifiers which affect collation and character set.

coll_id  coll_name        charset_name    is_builtin  has_expansions  contractions  uca_strength
================================================================================================
0        'iso88591_bin'   'iso88591'     'Yes'        'No'            0             'Not applicable'
1        'utf8_bin'       'utf8'         'Yes'        'No'            0             'Not applicable'
2        'iso88591_en_cs' 'iso88591'     'Yes'        'No'            0             'Not applicable'
3        'iso88591_en_ci' 'iso88591'     'Yes'        'No'            0             'Not applicable'
4        'utf8_en_cs'     'utf8'         'Yes'        'No'            0             'Not applicable'
5        'utf8_en_ci'     'utf8'         'Yes'        'No'            0             'Not applicable'
6        'utf8_tr_cs'     'utf8'         'Yes'        'No'            0             'Not applicable'
7        'utf8_ko_cs'     'utf8'         'Yes'        'No'            0             'Not applicable'
8        'euckr_bin'      'euckr'        'Yes'        'No'            0             'Not applicable'
9        'binary'         'binary'       'Yes'        'No'            0             'Not applicable'

<data_type> ::= <column_type> [<charset_modifier_clause>] [<collation_modifier_clause>]

<charset_modifier_clause> ::= {CHARACTER_SET | CHARSET} {<char_string_literal> | <identifier> }

<collation_modifier_clause> ::= {COLLATE } {<char_string_literal> | <identifier> }

CREATE TABLE t1 (s1 VARCHAR (100) CHARSET utf8);

ALTER TABLE t1 CHANGE s1 c1 CHAR(10) COLLATE utf8_en_cs;

SELECT CAST (c1 as VARCHAR(5) COLLATE 'iso88591_en_ci') FROM t1 ORDER BY 1;

SELECT c1 FROM t1 ORDER BY CAST (c1 as VARCHAR(5) COLLATE iso88591_en_ci);

CREATE TABLE t (
    s1 STRING COLLATE utf8_en_cs,
    s2 STRING COLLATE utf8_tr_cs
);

-- insert values into both columns

SELECT s1, s2 FROM t WHERE s1 > s2;

ERROR: '>' requires arguments with compatible collations.

SET NAMES utf8
PREPARE st FROM 'SELECT COLLATION(UPPER(?)) col1, COERCIBILITY(UPPER(?)) col2';
EXECUTE st USING 'a', 'a';

  col1                         col2
===================================
  'utf8_bin'                     -1

PREPARE st1 FROM 'SELECT INSERT(?,2,2,?)';
EXECUTE st1 USING _utf8'abcd', _binary'ef';

 insert( ?:0 , 2, 2,  ?:1 )
======================
 'aefd'

CREATE TABLE tbl (e ENUM (_utf8'a', _utf8'b'));

CREATE TABLE t (e ENUM (_utf8'a', _utf8'b') COLLATE utf8_bin);
CREATE TABLE t (e ENUM (_utf8'a', _utf8'b')) COLLATE utf8_bin;

Charset and Collation of Tables¶

The charset and the collation can be specified after the table creation syntax.

CREATE TABLE table_name (<column_list>)  [CHARSET charset_name] [COLLATE collation_name]

If the charset and the collation of a column are omitted, the charset and the collation of a table is used. If the charset and the collation of a table are omitted, the charset and the collation of a system is used.

The following shows how to specify the collation on the table.

CREATE TABLE tbl(
    i1 INTEGER,
    s STRING
) CHARSET utf8 COLLATE utf8_en_cs;

If the charset of a column is specified and the collation of a table is specified, the collation of this column is specified as the default collation(<collation_name>_bin) about this column's charset.

CREATE TABLE tbl (col STRING CHARSET utf8) COLLATE utf8_en_ci;

On the above query, the collation of the column col becomes utf8_bin, the default collation about this column.

csql> ;sc tbl

 <Class Name>

  tbl                  COLLATE utf8_en_ci

 <Attributes>

  col                  CHARACTER VARYING(1073741823) COLLATE utf8_bin

Charset and Collation of String Literals¶

The charset and the collation of a string literal are determined based on the following priority.

1. Charset Introducer introducer or COLLATE modifier of string literal
2. The charset and the collation defined by the SET NAMES Statement
3. System charset and collation(Default collation by the locale specified when creating DB)

SET NAMES [ charset_name ] [ COLLATE collation_name]

SET NAMES utf8;
SET SYSTEM PARAMETERS 'intl_collation=utf8_bin';

SELECT 'a';

SET NAMES utf8;
SELECT 'a';

[charset_introducer]'constant-string' [ COLLATE collation_name ]

SELECT 'cubrid';
SELECT _utf8'cubrid';
SELECT _utf8'cubrid' COLLATE utf8_en_cs;

SET NAMES utf8 COLLATE utf8_en_ci;
SELECT 'a' COLLATE utf8_en_cs;

Charset and Collation of Expressions¶

The charset and collation of expression's result are inferred from charset and collation of arguments in the expression. Collation inference in CUBRID is based on coercibility. For more information, see How to Determine Collation among Columns with Different Collation.

All string matching function(LIKE, REPLACE, INSTR, POSITION, LOCATE, SUBSTRING_INDEX, FIND_IN_SET, etc) and comparison operators(<, >, =, etc) take collation into account.

Charset and Collation of System Data¶

The system charset is taken from the locale specified when creating DB. The system collation is always the bin collation (<charset>_bin) of system charset. CUBRID supports three charset(iso88591, euckr, utf8), and accordingly three system collations(iso88591_bin, euckr_bin, utf8_bin).

Impact of Charset Specified When Creating DB¶

The locale specified when creating DB affects the following.

• Character supported in identifiers and casing rules (called "alphabet")
• Default locale for date - string conversion functions
• Default locale for number - string conversion functions
• Console conversion in CSQL

CREATE TABLE ÈABC;

String literal input and output¶

String literals data may be entered to CUBRID by various ways:

• API interface (CCI)
• language dependent interface - JDBC, Perl driver, etc.
• CSQL - command line from console or input file

When receiving character data through drivers, CUBRID cannot be aware of the charset of those strings. All text data contained between quotes (string literals) are handled by CUBRID as raw bytes; the charset meta-information must be provided by client. CUBRID provides a way for the client to instruct it about which type of encoding is using for its character data. This is done with the SET NAMES statement or with charset introducer.

<consoleconversion type="ISO88599" windows_codepage="28599" linux_charset="iso88599,ISO_8859-9,ISO8859-9,ISO-8859-9">

Contraction and Expansion of Collation¶

CUBRID supports contraction and expansion for collation. Contraction and expansion are available for UTF-8 charset collation. You can see the contraction and expansion of collation in the collation setting in the LDML file. Using contraction and expansion affects the size of locale data (shared library) and server performance.

Operations Requiring Collation and Charset¶

CREATE TABLE t (s_iso STRING CHARSET iso88591, s_utf8 STRING CHARSET utf8);
SET NAMES iso88591;
INSERT INTO t VALUES ('È','È');

-- the first returns 1, while the second does 2
SELECT OCTET_LENGTH(s_iso), OCTET_LENGTH(s_utf8) FROM t;

SET NAMES utf8;
CREATE TABLE t1(col1 CHAR(1));
INSERT INTO t1 VALUES ('Ç');

Collation settings impacting CUBRID features¶

s LIKE 'abc%' → s ≥ 'abc' AND s < 'abd' (if using strictly the English alphabet)

s LIKE 'abc%' → s ≥ 'abc' AND s < 'abd' and s LIKE 'abc%' (if using strictly the English alphabet)

Viewing Collation Information¶

To view the collation information, use CHARSET(), COLLATION() and COERCIBILITY() functions.

The information of the database collation can be shown on db_collation system view or SHOW COLLATION.

Compiling Timezone Library¶

To use a timezone by specifying a timezone region name, timezone library is required. This is provided as default when CUBRID is installed. By the way, to update a timezone region information as a latest one, timezone library should be compiled after updating a recent code which can be downloaded in IANA (http://www.iana.org/time-zones).

The following is a process to update timezone library as a recent one.

At first, stop cubrid service and operate the following process.

Locale¶

• By default, en_US gives best performance. If you have a plan to use only English, this is recommended.
• Using UTF-8 locale will increase storage requirement of fixed char(CHAR) by 4 times; using EUC-KR increases storage 3 times.
• If user string literals have different charset and collation from system, query strings will grow as the string literals are decorated with them.
• If localized (non-ASCII) characters will be used for identifiers, then use an .utf8 locale
• Once established the UTF-8 charset for DB, it is best to use a LDML locale (this ensures that identifier names containing most Unicode characters are correctly cased) than a system locale.
• Setting a locale affects also conversion functions(intl_date_lang, intl_number_lang).
• When you set the locale during creating DB, there should be no concern on charset and collation of string-literals or user tables columns; all of them can be changed at run-time (with CAST() in queries) or ALTER .. CHANGE for a permanent change.

CHAR and VARCHAR¶

• Generally, use VARCHAR if there are large variations in actual number of characters in user data.
• CHAR type is fixed length type. Therefore, Even if you store only English character in CHAR type, it requires 4 bytes storage in UTF-8 and 3 bytes in EUC-KR.
• The precision of columns refers to the number of characters (glyphs).
• After choosing precision, charset and collation should be set according to most used scenarios.

Choosing Charset¶

• Even if your text contains non-ASCII character, use utf8 or euckr charsets only if application requires character counting, inserting, replacing.
• For CHAR data, the main concern should be storage requirement (4x or utf8, 3x for euckr).
• For both CHAR and VARCHAR data, there is some overhead when inserting/updating data: counting the precision (number of characters) of each instance is more consuming for non-ISO charsets.
• In queries, charset of expressions may be converted using CAST() operator.

Choosing Collation¶

• If no collation dependent operations are performed (string searching, sorting, comparisons, casing), than choose bin collation for that charset or binary collation
• Collation may be easily overridden using CAST() operator, and COLLATE modifier (in 9.1 version) if charset is unchanged between original charset of expression and the new collation.
• Collation controls also the casing rules of strings
• Collations with expansions are slower, but are more flexible and they perform whole-word sorting

Normalization¶

• If your client applications send text data to CUBRID in decomposed form, then configure unicode_input_normalization = yes, so that CUBRID re-composes it and handles it in composed form
• If your client "knows" to handle data only in decomposed form, than set unicode_output_normalization = yes, so that CUBRID always sends in decomposed form.
• If the client "knows" both forms, then leave unicode_output_normalization = no

CAST vs COLLATE¶

• When building statements, the CAST() operator is more costly than COLLATE modifier (even more when charset conversion occurs).
• COLLATE modifier does not add an additional execution operator; using COLLATE modifier should enhance execution speed over using CAST() operator.
• COLLATE modifier can be used only when charset is not changed

LDML Calendar Information¶

• The first part consists in providing default CUBRID formats for DATE, DATETIME, TIME, TIMESTAMP, DATETIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE data type conversion to/from string. This formats are used by functions TO_DATE(), TO_TIME(), TO_DATETIME(), TO_TIMESTAMP(), TO_CHAR(), TO_DATETIME_TZ(), TO_TIMESTAMP_TZ(). The formats elements allowed depend on data type and are the ones used for TO_CHAR() function (Date/Time Format 1). Only ASCII characters are allowed in the format strings. The allowed size are 30 bytes (characters) for DATE and TIME formats, 48 characters for DATETIME and TIMESTAMP formats and 70 characters for DATETIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE.
• The <months> requires to specify the names for months in both long form and abbreviated form. The allowed size are 15 (or 60 bytes) for abbreviated form and 25 characters (or 100 bytes) for normal form.
• The <days> requires week day names in both long and abbreviated form. The allowed size are 10 characters (or 40 bytes) for abbreviated form and 15 characters (or 60 bytes) for full day name.
  • The <dayperiods> sub-tree requires to define the string for AM/PM format varints (according to type attribute). The allowed size is 10 characters (or 40 bytes).

The months and week-days names (in both long and abbreviated form) must be specified in Camel case format (first letter upper case, the rest in lower case). CUBRID checks only the maximum allowed size in bytes; the size in characters is computed only for full-width UTF-8 characters (4 bytes), so it would be possible to set a month name having 100 ASCII-only characters (the 25 characters limit is when each character from month name is encoded on 4 bytes in UTF-8).

LDML Numbers information¶

• The <symbols> tag defines the characters used as symbols for splitting decimal part from integer part in numbers and for grouping the digits. CUBRID expects only ASCII characters for these symbols. Empty of space character is not allowed. CUBRID performs grouping for 3 digits.

LDML Alphabet¶

These allow to define casing rules for alphabet of the locale. The 'CUBRIDAlphabetMode' attribute defines the primary source of data for characters. Normally, this should be set to "UNICODEDATAFILE", values which instructs CUBRID to use the Unicode data file ($CUBRID/locales/data/unicodedata.txt).

This file must not be modified, any customization on certain characters should be done in LDML file. If such value is configured, all Unicode characters up to codepoint 65535 are loaded with casing information. The other allowed value for CUBRIDAlphabetMode is "ASCII" which will lead to only ASCII character can be lower case, upper case or case-insenstive compare in matching functions.

This does not affect CUBRID's ability to support all UTF-8 4 bytes encoded Unicode characters, it just limits the casing ability for characters not included.

The casing rules are optional and apply on top of the primary source of character information (UNICODEDATAFILE or ASCII).

CUBRID allows to define upper casing rules (<u> tag) and lower casing rules (<l> tag).Each of upper and lower casing rules set consists for pairs of source-destination (<s> = source, <d> = destination). For instance, the following defines a rule that each character "A" is lower cased to "aa" (two character "a").

<l>
    <s>A</s>
    <d>aa</d>
</l>

LDML Console Conversion¶

In Windows, the console does not support UTF-8 encoding, so CUBRID allows to translate characters from their UTF-8 encoding to a desired encoding. After configuring console conversion for a locale, the user must set prior to starting CSQL application the codepage of the console using 'chcp' command (the codepage argument must match the 'windows_codepage' attribute in LDML). Conversion will work bidirectionally (input and output in CSQL), but is only limited to Unicode characters which can be converted in the configured codepage.

The <consoleconversion> element is optional and allows to instruct CSQL how to print (in which character encoding) the text in interactive command. The 'type' attribute defines the conversion scheme. The allowed values are:

• ISO: is a generic scheme in which the destination codepage is a single byte charset
• ISO88591: is a predefined single byte scheme for ISO-8859-1 charset (the 'file' attribute is not required, is ignored)
• ISO88599: is a predefined single byte scheme for ISO-8859-9 charset (also the 'file' attribute is not required)
• DBCS: Double Byte Code-Set; it is a generic scheme in which the destination codepage is a double byte charset

The 'windows_codepage' is the value for Windows codepage which CUBRID automatically activates console conversion. The 'linux_charset' is corresponding value for charset part in LANG environment variable from UNIX system. It is recommended to use native CUBRID charset in Linux console.

The 'file' attribute is required only for "ISO" and "DBCS" values of 'type' attribute and is the file containing the translation information ($CUBRID/locales/data/codepages/).

LDML Collation¶

Configuring a collation is the most complex task for adding LDML locale in CUBRID. Only collation having UTF-8 codeset can be configured. CUBRID allows to configure most constructs specified by UCA - Unicode Collation Algorithm (http://www.unicode.org/reports/tr10/) including contractions and expansions, but the properties for the collation are mostly controlled via the 'settings' attribute.

A LDML file can contain multiple collations. Collations can be included from external file using the 'include' tag. The 'validSubLocales' attribute of 'collations' tag is a filter allowing to control locale compilation when external collations (from external files) are included. Its values can be either a list of locales or "*" in which case the collations in sub-tree are added in all locales from which the file is included.

One collation is defined using the 'collation' tag and its sub-tree. The 'type' attribute indicates the name for the collation as it will be added in CUBRID. The 'settings' tag defines the properties of the collation:

• 'id' is the (internal) numeric identifier used by CUBRID. It is integer value in range (32 - 255) and is optional, but is strongly recommended that an explicit unassigned values is set. Please see Collation Naming Rules.
• 'strength' is a measure of how strings compare. See Collation Properties. The allowed values are :
  • "quaternary": different graphic symbols of the same character compare differently, but different Unicode codepoints may compare equal.
  • "tertiary": graphic symbols of the same character are equal, case-sensitive collation.
  • "secondary": case insensitive collation, characters with accents compare different
  • "primary": accents are ignored, all characters compare as the base character.
• 'caseLevel': special setting to enable case sensitive compare for collations having strength < tertiary. Valid values are "on" or "off".
• 'caseFirst': order of casing. Valid values are "lower", "upper" and "off". The "upper" values means upper case letters are ordered before the corresponding lower case letter.
• 'CUBRIDMaxWeights': it is the number of codepoints (or last codepoint + 1) which are customized in the collation. Maximum value is 65536. Increasing this value increases the size of collation data.
• 'DUCETContractions': valid values are "use" or "ignore". When "use" - enable CUBRID to use in the collation the contractions defined by DUCET file ($CUBRID/locales/data/ducet.txt) or ignoring them.
• 'TailoringContractions': same as previous but refers to the contractions defined or derived from explicit collation rules. Enabling contractions leads to a more complex collation (slower string compares).
• 'CUBRIDExpansions': allowed values are "use" or "ignore" (default) and refers to usage of collation expansions from both the DUCET file and tailoring rules; This has the most influence on collation properties. Enabling it will result in a compare with multiple passes (up to collation strength) when comparing strings. Also it greatly increases collation data, with the benefit of obtaining a more "natural" sort order. See Expansion.
• 'backwards': "on" or "off": used to obtain "french" order by performing an end-to-start compare on secondary level (for accents). It has effect only when 'CUBRIDExpansions' are enabled.
• 'MatchContractionBoundary': "true" or "false". This is used in collation having expansions and contractions to configure behavior at string matching when a contraction is found.

The main data for a collation is loaded from the DUCET file. After this step, the collation may be customized using "tailoring rules". These are the "<rules>" (LDML) and "<cubridrules>" (CUBRID specific).

The 'cubridrules' tag is optional and can be used to explicitely set weight values for a codepoint or a range of codepoints. The cubridrules apply after loading the primary collation data from DUCET file and before applying the UCA rules (from '<rules>' tag). Each of these rule is enclosed in '<set>' tag. If the rule refers to only one Unicode codepoint, then a '<scp>' tag is provided which contains the hexadecimal value of codepoint.

All available CUBRID collations contain this cubrid-rule:

<cubridrules>
    <set>
        <scp>20</scp>
        <w>[0.0.0.0]</w>
    </set>
</cubridrules>

This rule says that weight values (UCA defines four weight values per collation element) of the codepoints starting with 20 (which is ASCII space character) are all set to zero. Since there is no '<ecp>' tag, the only codepoint affected is 20. In CUBRID, space character compares as zero. The allowed tags inside of a '<set>' rule are:

• '<cp>': rule to set the weights for single codepoint.
• '<ch>': rule to set the weights for single character. Similar to previous one, but instead of codepoint it expects a Unicode character (in UTF-8 encoding).
• '<scp>': rule to set the weights for a range of codepoints. This is the starting codepoint.
• '<sch>': rule to set the weights for a range of characters. This is the starting character. In this context, the order of characters is given by their Unicode codepoints.
• '<ecp>': end codepoint for a range rule.
• '<ech>': end character for a range rule.
• '<w>': weight values to set (single value). The weight values are expected in hexadecimal. Each collation element has four values which are delimited by point and enclosed by square brackets([]). There can be up to 10 collation elements.
• '<wr>': starting weight values to set for a range. Optionally, there is 'step' attribute of this tag, which sets the increasing step after each codepoint. By default the step is [0001.0000.0000.0000], which means that after setting the first weight values for the starting codepoint, one value is added to primary level weight and set to the next codepoint in range, and the process is repeated until end codepoint.

Examples:

<cubridrules>
    <set>                            <!-- Rule 1 -->
        <scp>0</scp>
        <ecp>20</ecp>
        <w>[0.0.0.0]</w>
    </set>

    <set>                            <!-- Rule 2 -->
        <scp>30</scp>
        <ecp>39</ecp>
        <wr step="[1.0.0.0][1.0.0.0]">[30.0.0.0][30.0.0.0]</wr>
    </set>

</cubridrules>

The Rule 1, sets for codepoints ranging from 0 to 20 (including) the weight values 0. The Rule 2, sets for codepoints ranging from 30 to 39 (which are the digits), a set of two collation elements with increasing weights; In this example, codepoint 39 (character "9") will have the weights with two collation elements [39.0.0.0][39.0.0.0].

The '<rules>' tag is also optional but is according to LDML and UCA specifications. The meanings of sub-ordinates tags are :

• '<reset>': anchor collation element. It defines the reference to which subsequent rules (up to next <reset>) are tailored. It can be a single characters or multiple characters in which case is either a contraction or an expansion. By default, all the tailoring rules after the anchor are sort "after" (element from first rule is after the anchor, element from second rule sorts after element in first rule); if the optional attribute "before" is present, then only the first rule after the <reset> sorts before the anchor, while the second and the following rules resumes the normal "after" sort (element in second rule sorts after element in first rule).
• '<p>': the character comes after (or before, if the anchor had the before attribute) the previously tailored one at primary level.
• '<s>': the character comes after (or before, if the anchor had the before attribute) the previously tailored one at secondary level.
• '<t>': the character comes after (or before, if the anchor had the before attribute) the previously tailored one at tertiary level.
• '<i>': the character sorts identically to previous one
• '<pc>', '<sc>', '<tc>', '<ic>': same as '<p>', '<s>', '<t>', '<i>' but applies to a range of characters
• '<x>': specifies the expansion character
• '<extend>': specifies the second character of expansion.
• '<context>': specifies the context in which a rule applies. A variant to specify contractions and expansions.

For more information on UCA tailoring with LDML rules see http://www.unicode.org/reports/tr35/tr35-collation.html.

Footnotes